package com.ynny.spring.student.systemDB.dao;

import com.ynny.spring.student.systemDB.bean.Course;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

@Component("courseManage")
public class CourseManageImpl implements CourseManage {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void updateCourse(String courseName) {
        // 定义SQL更新语句，实现根据课程名更新待选课程的剩余名额
        String sql = "update courses set " + courseName + " = " + courseName + " - 1";
        // 执行SQL更新语句，返回执行更新操作的记录条数
        int num = jdbcTemplate.update(sql);
        if (num <= 0)
            System.out.println("更新待选课程记录失败");
    }

    @Override
    public void queryCourse() {
        // 定义SQL查询语句
        String sql = "select * from courses";
        // 创建一个BeanPropertyRowMapper对象
        RowMapper<Course> rowMapper = new BeanPropertyRowMapper<>(Course.class);
        // 执行SQL查询语句，并通过rowMapper返回Course类对象
        List<Course> courses = jdbcTemplate.query(sql, rowMapper);
        for (Course course : courses) {
            System.out.println(course);
        }
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED, readOnly = false, isolation = Isolation.DEFAULT)
    public void selectCourse(String number, String courseName) {
        updateCourse(courseName);
        queryCourse();
        // 使用execute方法执行SQL创建表语句，创建选课信息表optional_course
        jdbcTemplate.execute("create table IF not exists optional_course (" +
                "number varchar(20), courseName varchar(20), score double)");
        String sql = "insert into optional_course (number, courseName, score) values (?, ?, ?)";

        // 执行SQL插入语句
        jdbcTemplate.update(sql, number, courseName, 0.0);
        System.out.println("查询选课程信息记录");

        // 定义SQL查询语句
        sql = "select * from optional_course where number = ?";

        // 执行SQL查询语句，并返回存放Map对象的List集合
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, number);
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }
}
